const { createConnection } = require('./init');
const { getBeijingTime } = require('../utils/timeUtils');

// 执行查询的通用函数
function runQuery(sql, params = []) {
  return new Promise((resolve, reject) => {
    const db = createConnection();
    db.run(sql, params, function(err) {
      if (err) {
        reject(err);
      } else {
        resolve({ lastID: this.lastID, changes: this.changes });
      }
      db.close();
    });
  });
}

// 执行查询并返回单行结果
function getQuery(sql, params = []) {
  return new Promise((resolve, reject) => {
    const db = createConnection();
    db.get(sql, params, (err, row) => {
      if (err) {
        reject(err);
      } else {
        resolve(row);
      }
      db.close();
    });
  });
}

// 执行查询并返回多行结果
function allQuery(sql, params = []) {
  return new Promise((resolve, reject) => {
    const db = createConnection();
    db.all(sql, params, (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows);
      }
      db.close();
    });
  });
}

// 用户相关查询
const userQueries = {
  // 根据用户名查找用户
  findByUsername: (username) => {
    return getQuery('SELECT * FROM users WHERE username = ?', [username]);
  },
  
  // 根据用户ID查找用户
  findById: (userId) => {
    return getQuery('SELECT * FROM users WHERE id = ?', [userId]);
  },
  
  // 创建新用户
  create: (username, hashedPassword, email, isAdmin = false, canInvite = false) => {
    return runQuery(
      'INSERT INTO users (username, password, email, is_admin, can_invite) VALUES (?, ?, ?, ?, ?)',
      [username, hashedPassword, email, isAdmin, canInvite]
    );
  },
  
  // 更新用户权限
  updatePermissions: (userId, isAdmin, canInvite) => {
    return runQuery(
      'UPDATE users SET is_admin = ?, can_invite = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [isAdmin, canInvite, userId]
    );
  },
  
  // 获取所有用户
  findAll: () => {
    return allQuery('SELECT id, username, email, is_admin, can_invite, created_at FROM users ORDER BY created_at DESC');
  },
  
  // 检查是否有管理员
  hasAdmin: () => {
    return getQuery('SELECT COUNT(*) as count FROM users WHERE is_admin = 1');
  }
};

// 流量配置相关查询
const trafficConfigQueries = {
  // 创建流量配置
  create: (userId, token, phone, operator, appId) => {
    return runQuery(
      'INSERT INTO traffic_configs (user_id, token, phone, operator, app_id) VALUES (?, ?, ?, ?, ?)',
      [userId, token, phone, operator, appId]
    );
  },
  
  // 根据用户ID查找配置
  findByUserId: (userId) => {
    return allQuery('SELECT * FROM traffic_configs WHERE user_id = ? AND is_active = 1', [userId]);
  },
  
  // 根据ID查找配置
  findById: (id) => {
    return getQuery('SELECT * FROM traffic_configs WHERE id = ?', [id]);
  },
  
  // 删除配置
  delete: (id, userId) => {
    return runQuery('UPDATE traffic_configs SET is_active = 0 WHERE id = ? AND user_id = ?', [id, userId]);
  }
};

// 流量详情相关查询
const trafficDetailQueries = {
  // 创建流量详情记录（自动维护最多10条记录）
  create: async (configId, data) => {
    const db = createConnection();
    
    return new Promise((resolve, reject) => {
      db.serialize(() => {
        db.run('BEGIN TRANSACTION');
        
        // 插入新记录（使用北京时间）
        const beijingTime = getBeijingTime();
        
        db.run(
          `INSERT INTO traffic_details 
           (config_id, total_traffic, used_traffic, remaining_traffic, 
            general_used, general_remaining, directed_used, directed_remaining, jump_points, freeflow, package_name, query_time, detailed_data, data_source) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            configId, data.totalTraffic, data.usedTraffic, data.remainingTraffic,
            data.generalUsed, data.generalRemaining, data.directedUsed, data.directedRemaining, data.jumpPoints, data.freeflow || 0, data.packageName, beijingTime,
            JSON.stringify(data.detailedData || null), data.dataSource || 'legacy'
          ],
          function(err) {
            if (err) {
              db.run('ROLLBACK');
              db.close();
              reject(err);
              return;
            }
            
            const insertId = this.lastID;
            
            // 删除多余的历史记录，只保留最新的10条
            db.run(
              `DELETE FROM traffic_details 
               WHERE config_id = ? 
               AND id NOT IN (
                 SELECT id FROM traffic_details 
                 WHERE config_id = ? 
                 ORDER BY query_time DESC 
                 LIMIT 10
               )`,
              [configId, configId],
              function(err) {
                if (err) {
                  db.run('ROLLBACK');
                  db.close();
                  reject(err);
                  return;
                }
                
                db.run('COMMIT');
                db.close();
                resolve({ lastID: insertId, changes: this.changes });
              }
            );
          }
        );
      });
    });
  },
  
  // 根据配置ID查找详情（默认最多10条）
  findByConfigId: (configId, limit = 10) => {
    return allQuery(
      'SELECT *, detailed_data, data_source FROM traffic_details WHERE config_id = ? ORDER BY query_time DESC LIMIT ?',
      [configId, Math.min(limit, 10)] // 确保不超过10条
    );
  },
  
  // 获取最新的流量详情
  findLatestByConfigId: (configId) => {
    return getQuery(
      'SELECT *, detailed_data, data_source FROM traffic_details WHERE config_id = ? ORDER BY query_time DESC LIMIT 1',
      [configId]
    );
  },
  
  // 清理所有配置的历史记录，每个配置只保留最新的10条
  cleanupHistoryRecords: async () => {
    const db = createConnection();
    
    return new Promise((resolve, reject) => {
      db.serialize(() => {
        db.run('BEGIN TRANSACTION');
        
        // 获取所有配置ID
        db.all('SELECT DISTINCT config_id FROM traffic_details', [], (err, rows) => {
          if (err) {
            db.run('ROLLBACK');
            db.close();
            reject(err);
            return;
          }
          
          let processed = 0;
          const total = rows.length;
          
          if (total === 0) {
            db.run('COMMIT');
            db.close();
            resolve({ cleaned: 0 });
            return;
          }
          
          // 为每个配置清理历史记录
          rows.forEach((row) => {
            db.run(
              `DELETE FROM traffic_details 
               WHERE config_id = ? 
               AND id NOT IN (
                 SELECT id FROM traffic_details 
                 WHERE config_id = ? 
                 ORDER BY query_time DESC 
                 LIMIT 10
               )`,
              [row.config_id, row.config_id],
              function(err) {
                if (err) {
                  db.run('ROLLBACK');
                  db.close();
                  reject(err);
                  return;
                }
                
                processed++;
                if (processed === total) {
                  db.run('COMMIT');
                  db.close();
                  resolve({ cleaned: total });
                }
              }
            );
          });
        });
      });
    });
  }
};

// 监控链接相关查询
const monitorLinkQueries = {
  // 创建监控链接（自动维护最多5条记录）
  create: async (configId, monitorUrl, monitorToken) => {
    const db = createConnection();
    
    return new Promise((resolve, reject) => {
      db.serialize(() => {
        db.run('BEGIN TRANSACTION');
        
        // 插入新的监控链接
        db.run(
          `INSERT INTO monitor_links (config_id, monitor_url, monitor_token) VALUES (?, ?, ?)`,
          [configId, monitorUrl, monitorToken],
          function(err) {
            if (err) {
              db.run('ROLLBACK');
              db.close();
              reject(err);
              return;
            }
            
            const insertId = this.lastID;
            
            // 删除多余的监控链接，只保留最新的5条
            db.run(
              `DELETE FROM monitor_links 
               WHERE config_id = ? 
               AND id NOT IN (
                 SELECT id FROM monitor_links 
                 WHERE config_id = ? 
                 ORDER BY created_at DESC 
                 LIMIT 5
               )`,
              [configId, configId],
              function(err) {
                if (err) {
                  db.run('ROLLBACK');
                  db.close();
                  reject(err);
                  return;
                }
                
                db.run('COMMIT');
                db.close();
                resolve({ lastID: insertId, changes: this.changes });
              }
            );
          }
        );
      });
    });
  },
  
  // 根据配置ID查找监控链接
  findByConfigId: (configId) => {
    return allQuery(
      'SELECT * FROM monitor_links WHERE config_id = ? AND is_active = 1 ORDER BY created_at DESC LIMIT 5',
      [configId]
    );
  },
  
  // 根据监控令牌查找配置
  findConfigByToken: (monitorToken) => {
    return getQuery(
      `SELECT tc.* FROM traffic_configs tc 
       JOIN monitor_links ml ON tc.id = ml.config_id 
       WHERE ml.monitor_token = ? AND ml.is_active = 1 AND tc.is_active = 1`,
      [monitorToken]
    );
  },
  
  // 删除监控链接
  delete: (id, configId, userId) => {
    return runQuery(
      `UPDATE monitor_links SET is_active = 0 
       WHERE id = ? AND config_id IN (
         SELECT id FROM traffic_configs WHERE id = ? AND user_id = ?
       )`,
      [id, configId, userId]
    );
  }
};

// 邀请码相关查询
const inviteCodeQueries = {
  // 创建邀请码
  create: (code, createdBy = null, options = {}) => {
    const {
      expiresAt = null,
      maxUses = 1,
      inviteType = 'INVITE',
      description = null
    } = options;
    
    return runQuery(
      'INSERT INTO invite_codes (code, created_by, expires_at, max_uses, invite_type, description) VALUES (?, ?, ?, ?, ?, ?)',
      [code, createdBy, expiresAt, maxUses, inviteType, description]
    );
  },
  
  // 根据邀请码查找
  findByCode: (code) => {
    return getQuery(`
      SELECT * FROM invite_codes 
      WHERE code = ? AND is_active = 1 
      AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)
    `, [code]);
  },
  
  // 使用邀请码
  useCode: (code, usedBy) => {
    const db = createConnection();
    
    return new Promise((resolve, reject) => {
      db.serialize(() => {
        db.run('BEGIN TRANSACTION');
        
        // 检查邀请码是否可用
        db.get(`
          SELECT * FROM invite_codes 
          WHERE code = ? AND is_active = 1 
          AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)
          AND (max_uses IS NULL OR use_count < max_uses)
        `, [code], (err, row) => {
          if (err) {
            db.run('ROLLBACK');
            db.close();
            reject(err);
            return;
          }
          
          if (!row) {
            db.run('ROLLBACK');
            db.close();
            reject(new Error('邀请码无效或已过期'));
            return;
          }
          
          // 更新使用次数
          const newUseCount = (row.use_count || 0) + 1;
          const isFullyUsed = row.max_uses && newUseCount >= row.max_uses;
          
          db.run(`
            UPDATE invite_codes 
            SET use_count = ?, is_used = ?, used_by = ?, used_at = CURRENT_TIMESTAMP 
            WHERE code = ?
          `, [newUseCount, isFullyUsed ? 1 : 0, usedBy, code], function(err) {
            if (err) {
              db.run('ROLLBACK');
              db.close();
              reject(err);
              return;
            }
            
            db.run('COMMIT');
            db.close();
            resolve({ lastID: this.lastID, changes: this.changes });
          });
        });
      });
    });
  },
  
  // 获取用户创建的邀请码
  findByCreator: (createdBy, limit = null) => {
    const limitClause = limit ? `LIMIT ${limit}` : '';
    return allQuery(`
      SELECT * FROM invite_codes 
      WHERE created_by = ? 
      ORDER BY created_at DESC 
      ${limitClause}
    `, [createdBy]);
  },
  
  // 获取所有邀请码
  findAll: (filters = {}) => {
    let whereClause = '1=1';
    const params = [];
    
    if (filters.inviteType) {
      whereClause += ' AND ic.invite_type = ?';
      params.push(filters.inviteType);
    }
    
    if (filters.isUsed !== undefined) {
      whereClause += ' AND ic.is_used = ?';
      params.push(filters.isUsed);
    }
    
    if (filters.createdBy) {
      whereClause += ' AND ic.created_by = ?';
      params.push(filters.createdBy);
    }
    
    return allQuery(`
      SELECT ic.*, 
             creator.username as creator_name,
             user.username as user_name
      FROM invite_codes ic
      LEFT JOIN users creator ON ic.created_by = creator.id
      LEFT JOIN users user ON ic.used_by = user.id
      WHERE ${whereClause}
      ORDER BY ic.created_at DESC
    `, params);
  },
  
  // 删除邀请码
  delete: (id) => {
    return runQuery('UPDATE invite_codes SET is_active = 0 WHERE id = ?', [id]);
  },
  
  // 批量删除邀请码
  deleteBatch: (ids) => {
    if (!ids || ids.length === 0) {
      return Promise.resolve({ changes: 0 });
    }
    
    const placeholders = ids.map(() => '?').join(',');
    return runQuery(
      `UPDATE invite_codes SET is_active = 0 WHERE id IN (${placeholders})`,
      ids
    );
  },
  
  // 清理过期邀请码
  cleanupExpired: () => {
    return runQuery(`
      UPDATE invite_codes 
      SET is_active = 0 
      WHERE expires_at IS NOT NULL 
      AND expires_at <= CURRENT_TIMESTAMP 
      AND is_active = 1
    `);
  },
  
  // 获取邀请码统计信息
  getStats: (createdBy = null) => {
    let whereClause = '1=1';
    const params = [];
    
    if (createdBy) {
      whereClause += ' AND created_by = ?';
      params.push(createdBy);
    }
    
    return getQuery(`
      SELECT 
        COUNT(*) as total_codes,
        SUM(CASE WHEN is_used = 1 THEN 1 ELSE 0 END) as used_codes,
        SUM(CASE WHEN is_used = 0 AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP) THEN 1 ELSE 0 END) as available_codes,
        SUM(CASE WHEN expires_at IS NOT NULL AND expires_at <= CURRENT_TIMESTAMP THEN 1 ELSE 0 END) as expired_codes,
        SUM(use_count) as total_uses
      FROM invite_codes 
      WHERE ${whereClause} AND is_active = 1
    `, params);
  },
  
  // 获取邀请码使用历史
  getUsageHistory: (code, limit = 10) => {
    return allQuery(`
      SELECT ic.*, u.username as used_by_name, ic.used_at
      FROM invite_codes ic
      LEFT JOIN users u ON ic.used_by = u.id
      WHERE ic.code = ? AND ic.is_used = 1
      ORDER BY ic.used_at DESC
      LIMIT ?
    `, [code, limit]);
  }
};

// 系统配置相关查询
const systemConfigQueries = {
  // 获取配置
  get: (key) => {
    return getQuery('SELECT value FROM system_config WHERE key = ?', [key]);
  },
  
  // 设置配置
  set: (key, value, description = null) => {
    return runQuery(
      `INSERT OR REPLACE INTO system_config (key, value, description, updated_at) 
       VALUES (?, ?, ?, CURRENT_TIMESTAMP)`,
      [key, value, description]
    );
  },
  
  // 获取所有配置
  getAll: () => {
    return allQuery('SELECT * FROM system_config ORDER BY key');
  }
};

module.exports = {
  runQuery,
  getQuery,
  allQuery,
  userQueries,
  trafficConfigQueries,
  trafficDetailQueries,
  monitorLinkQueries,
  inviteCodeQueries,
  systemConfigQueries
};
